'''
Author xzk
Create Time 2020.7.17上午
Change Time1 2020.7.17上午
Pull Time 2020.7.17晚上
Results 对数据库的插入，更新操作
'''

import pymysql
import datetime
from parking_database.query import query_one_parking_space, query_carinpark_by_plate, query_all_parking_space, query_car_plate


# 按车牌查询未出库记录（车牌号）  返回值：True(在库中) False(不在库中)
def query_is_carinpark_by_plate(car_plate):
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select * from parking_record where car_plate=%s and leave_time is null"
    cls.execute(sql, [car_plate])
    result = cls.fetchone()
    if result is None:
        isin = False
    elif result is not  None:
        isin = True
    conn.close()
    return isin


# 查询指定车牌是否存在slots表中 返回值：True（存在） False（不存在）
def query_car_plate_is_exist(car_plate):
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "select * from slots where Car_Plate=%s"
    cls.execute(sql, [car_plate])
    result = cls.fetchone()
    if result is None:
        IsExist = False
    elif result is not None:
        IsExist = True
    conn.close()
    return IsExist


# 停车信息录入（车位ID，车牌号，车型） 更新slots表停车位信息
def input_enter_info(id, car_plate=None, car_kind=None):
    if query_car_plate(car_plate) == id:
        #print("diu")
        print(car_plate, id,'diu')
    else:
        conn = pymysql.connect(
            host="cdb-4o4cwcko.bj.tencentcdb.com",
            port=10083,
            db="car",
            user="root",
            password="njlj2666",
            charset="utf8"
        )
        cls = conn.cursor()
        sql = "update slots set Car_Plate=%s, Car_Kind=%s, IsFree=%s where ID=%s"
        try:
            print(car_plate,car_kind,'newinsert')
            if (car_plate == None or car_plate=='') and (car_kind == None or car_plate==''):
                cls.execute(sql, [car_plate, car_kind, 0, id])
                print('none')

            else:
                cls.execute(sql, [car_plate, car_kind, 1, id])
                print('true')
            conn.commit()
        except:
            conn.rollback()
        finally:
            conn.close()

# 清空停车位信息 slots表
def clear_slot():
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "update slots set Car_Plate=%s, Car_Kind=%s, IsFree=%s "
    try:
        cls.execute(sql, [None, None, 0])
        conn.commit()
    except:
        conn.rollback()
    finally:
        conn.close()
'''
# 车辆离库信息录入（车牌号）
def input_leave_info(car_plate):
    conn = pymysql.connect(
        host="cdb-4o4cwcko.bj.tencentcdb.com",
        port=10083,
        db="car",
        user="root",
        password="njlj2666",
        charset="utf8"
    )
    cls = conn.cursor()
    sql = "update slots set Car_Plate=%s, Car_Kind=%s, IsFree=%s where Car_Plate=%s"
    try:
        cls.execute(sql, [None, None, 0, car_plate])
        conn.commit()
    except:
        conn.rollback()
    finally:
        conn.close()
'''


# （入库）停车记录录入（车牌号） 更新parking——record表
def input_entry_record(car_plate):
    car_leave = query_carinpark_by_plate(car_plate)
    if car_leave is None:
        conn = pymysql.connect(
            host="cdb-4o4cwcko.bj.tencentcdb.com",
            port=10083,
            db="car",
            user="root",
            password="njlj2666",
            charset="utf8"
        )
        cls = conn.cursor()
        sql = "insert into parking_record values(null,%s,null,null,null)"
        try:
            cls.execute(sql, [car_plate])
            conn.commit()
        except:
            conn.rollback()
        finally:
            conn.close()
    else:
        print('%s已在车库中。' % car_plate)


# （出库）停车记录完成（车牌号） 返回值：入库时间  更新parking_record表
def input_leave_record(car_plate):
    car_leave = query_carinpark_by_plate(car_plate)
    if car_leave is not None:
        conn = pymysql.connect(
            host="cdb-4o4cwcko.bj.tencentcdb.com",
            port=10083,
            db="car",
            user="root",
            password="njlj2666",
            charset="utf8"
        )
        cls = conn.cursor()
        sql = "update parking_record set leave_time=%s where id=%s"

        now_stamp = datetime.datetime.now()+datetime.timedelta(hours=8)
        entry_stamp = car_leave[2]
        time_park = now_stamp - entry_stamp
        result_park = {'天数': time_park.days, '小时': time_park.seconds//3600, '分钟': (time_park.seconds%3600)//60}

        try:
            cls.execute(sql, [now_stamp, car_leave[0]])
            conn.commit()
            return entry_stamp, result_park
        except:
            conn.rollback()
        finally:
            conn.close()
    else:
        print('%s不在车库中。' % car_plate)


if __name__ == '__main__':
    # input_leave_info('鄂B56U47')
    # print(input_leave_record('云ATD236'))
    # input_enter_info(2)
    #input_enter_info(2,'鲁BD8J53','黑色大众帕萨特(Passat)(领驭)')
    input_entry_record('鲁BD8J53')
    #input_leave_record('鲁BD8J53')


